/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package data.analysis;

import static com.sun.org.apache.xerces.internal.util.XMLChar.trim;
import com.sun.org.apache.xpath.internal.functions.Function;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import static java.lang.System.*;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 *
 * @author dungdv3
 */
public class DataAnalysis {

    //DB config
    private static final String url = "jdbc:mysql://localhost:3306/";
    private static final String dbName = "2014_emails_validated";
    private static final String driver = "com.mysql.jdbc.Driver";
    private static final String userName = "root";
    private static final String password = "";

    //DB connection
    private static Connection conn;

    //Check email pattern
    private static final String EMAIL_PATTERN
            = "^[_A-Za-z0-9-\\+]+(\\.[_A-Za-z0-9-]+)*@"
            + "[A-Za-z0-9-]+(\\.[A-Za-z0-9]+)*(\\.[A-Za-z]{2,})$";

    //Check email adress have only digit
    private static final String REGEX = "[0-9]+";

    /**
     * Get DB connection
     */
    private static void initConnectionDB() {
        try {
            DataAnalysis.conn = DriverManager.getConnection(DataAnalysis.url + DataAnalysis.dbName+"?useUnicode=true&characterEncoding=utf-8", DataAnalysis.userName, DataAnalysis.password);
        } catch (SQLException ex) {
            Logger.getLogger(DataAnalysis.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    /**
     * Import email to DB from file only has email
     * @throws FileNotFoundException
     * @throws IOException 
     */
    private static void ImportEmailFromFileEmailOnly() throws FileNotFoundException, IOException {
        BufferedReader reader = new BufferedReader(new FileReader("E://Email_Data/emailbaomoi.csv"));
        String line = null;
        int i = 1;
        while ((line = reader.readLine()) != null) {
            line = trim(line).toLowerCase();
            if (check_email(line)) {
                try {
                    Statement st = DataAnalysis.conn.createStatement();
                    String query_check = "select * from email_info where `email` = ?";
                    PreparedStatement preparedStmt_check = DataAnalysis.conn.prepareStatement(query_check);
                    preparedStmt_check.setString(1, line);
                    ResultSet res = preparedStmt_check.executeQuery();

                    if (!res.next()) {
                        String query = "insert into email_info (email, name, phone) values (?, ?, ?)";
                        PreparedStatement preparedStmt = DataAnalysis.conn.prepareStatement(query);
                        preparedStmt.setString(1, line);
                        preparedStmt.setString(2, "");
                        preparedStmt.setString(3, "");

                        preparedStmt.execute();

                        out.println(i + ". " + line + " is imported");
                    } else {
                        out.println(i + ". " + line + " is existed");
                    }

                } catch (SQLException ex) {
                    Logger.getLogger(DataAnalysis.class.getName()).log(Level.SEVERE, null, ex);
                }

            } else {
                out.println(i + ". " + line + " is not valid");
            }

            i++;
        }
    }

    /**
     * author: dungdv3
     * Import email to DB from file include information name & phone
     * @throws FileNotFoundException
     * @throws IOException 
     */
    private static void ImportEmailFromFileHasInfo() throws FileNotFoundException, IOException {
        BufferedReader reader = new BufferedReader(new FileReader("E://Email_Data/emailsurvey.txt"));
        String line = null;
        int i = 1;
        while ((line = reader.readLine()) != null) {
            String[] line_part = new String[3];
            line_part = line.split(";");
            String email = trim(line_part[1]).toLowerCase();
            String name = (!"Mời bạn điền vào đây".equals(line_part[0])) ? line_part[0] : "";
            String phone = (line_part.length == 3 && line_part[2].matches(REGEX)) ? line_part[2] : "";

            if (check_email(email)) {
                try {
                    Statement st = DataAnalysis.conn.createStatement();
                    String query_check = "select * from email_info where `email` = ?";
                    PreparedStatement preparedStmt_check = DataAnalysis.conn.prepareStatement(query_check);
                    preparedStmt_check.setString(1, email);
                    ResultSet res = preparedStmt_check.executeQuery();

                    if (!res.next()) {
                        String query = "insert into email_info (email, name, phone, validated) values (?, ?, ?, 1)";
                        PreparedStatement preparedStmt = DataAnalysis.conn.prepareStatement(query);
                        preparedStmt.setString(1, email);
                        preparedStmt.setString(2, name);
                        preparedStmt.setString(3, phone);

                        preparedStmt.execute();

                        out.println(i + ". " + email + " is imported");
                    } else {
                        String query = "update email_info set `name` = ?, `phone`=?, validated = 1 where `email` = ?";
                        PreparedStatement preparedStmt = DataAnalysis.conn.prepareStatement(query);
                        preparedStmt.setString(1, name);
                        preparedStmt.setString(2, phone);
                        preparedStmt.setString(3, email);

                        preparedStmt.execute();

                        out.println(i + ". " + email + " is updated");
                    }

                } catch (SQLException ex) {
                    out.println(ex);
                    return;
                }

            } else {
                out.println(i + ". " + email + " is not valid");
            }

            i++;
        }
    }

    /**
     * Function check all validate of email, only alow yahoo mail and gmail
     * @param email
     * @return true|false
     */
    public static boolean check_email(String email) {
        if (validate(email)) {
            String[] email_part = email.split("@");
            if ("gmail.com".equals(email_part[1]) || "yahoo.com".equals(email_part[1]) || "yahoo.com.vn".equals(email_part[1])) {
                return !email_part[0].matches(REGEX);
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    /**
     * Check only regular expression of email
     *
     * @param email
     * @return true|false
     */
    public static boolean validate(final String email) {
        Pattern pattern = Pattern.compile(EMAIL_PATTERN);
        Matcher matcher = pattern.matcher(email);
        return matcher.matches();
    }

    /**
     * @param args the command line arguments
     * @throws java.io.IOException
     */
    public static void main(String[] args) throws IOException {
        try {
            DataAnalysis.initConnectionDB();
            DataAnalysis.ImportEmailFromFileHasInfo();
            DataAnalysis.conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataAnalysis.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

}